# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: http://doc.scrapy.org/en/latest/topics/item-pipeline.html
from scrapy import log
from twisted.enterprise import adbapi
from scrapy.http import Request
from scrapy.exceptions import DropItem
import MySQLdb
import MySQLdb.cursors
from random import Random
import json
import re

class SpiderPipeline(object):

    def __init__(self):
        self.dbpool = adbapi.ConnectionPool('MySQLdb',  
                db = 'spider',  
                user = 'spider',  
                passwd = 'Xjk@4047218?',  
                cursorclass = MySQLdb.cursors.DictCursor,  
                charset = 'utf8',  
                use_unicode = False  
        )
    

#####################################################################################################################################
    def process_item(self, item, spider):
#####################################################################################################################################
        if item.get("type") == "car":
            query = self.dbpool.runInteraction(self._contidional_insert,item)
            #query.addErrback(self.handle_error)
        elif item.get("type") == "carmodel":
            query = self.dbpool.runInteraction(self._carmodel_insert,item)
            #query.addErrback(self.handle_error)
        elif item.get("type") == "carshop":
            query = self.dbpool.runInteraction(self._carshop_insert,item)
            #query.addErrback(self.handle_error)
        elif item.get("type") == "page":
            query = self.dbpool.runInteraction(self._page_insert,item)
            #query.addErrback(self.handle_error)
        elif item.get("type") == "price":
            query = self.dbpool.runInteraction(self._price_insert,item)
            #query.addErrback(self.handle_error)
#####################################################################################################################################
        elif item.get("type") == "bitcar":      
            query = self.dbpool.runInteraction(self._bitcar_insert,item)
            #query.addErrback(self.handle_error)
        elif item.get("type") == "bitcity":      
            query = self.dbpool.runInteraction(self._bitcity_insert,item)
            #query.addErrback(self.handle_error)
        elif item.get("type") == "bitpage":      
            query = self.dbpool.runInteraction(self._bitpage_insert,item)
            #query.addErrback(self.handle_error)
        elif item.get("type") == "bitcarshop":      
            query = self.dbpool.runInteraction(self._bitcarshop_insert,item)
            #query.addErrback(self.handle_error)
        elif item.get("type") == "bitcarmodel":      
            query = self.dbpool.runInteraction(self._bitcarmodel_insert,item)
            #query.addErrback(self.handle_error)
        elif item.get("type") == "bitlistpage":      
            query = self.dbpool.runInteraction(self._bitlistpage_insert,item)
            #query.addErrback(self.handle_error)
        elif item.get("type") == "bitprice":      
            query = self.dbpool.runInteraction(self._bitprice_insert,item)
            #query.addErrback(self.handle_error)
        return item
#####################################################################################################################################

    def _bitcar_insert(self,tx,item):
        baseUrl = "http://car.bitauto.com"
        brandurl = baseUrl+item["brandurl"][0]
        tx.execute('insert into sp_ba_brand(`name`,`url`) values(%s,%s)',(item['brand'][0].encode('utf-8'),brandurl))
        pid = tx.lastrowid
        for i in range(len(item["carstyle"])):
            cstyleUrl = baseUrl+item["carstyleurl"][i]
            tx.execute('insert into sp_ba_carstyle(`pid`,`name`,`url`) values(%s,%s,%s)',(pid,item["carstyle"][i],cstyleUrl))

    def _bitcity_insert(self,tx,item):
        for i in range(len(item["city"])):
            tx.execute('insert into sp_ba_city(`name`,`url`) values(%s,%s)',(item['city'][i].encode('utf-8'),item['cityurl'][i]))
                           
    def _bitpage_insert(self,tx,item):
        sql = 'update sp_ba_cstemp set pages ='+str(item["page"])+' where url like "%'+item["carshopurl"]+'%"'
        print sql
        tx.execute(sql)
                
    def _bitcarshop_insert(self,tx,item):
        for i in range(len(item["carshop"])):
            temp = unicode(item["tel"])
            p = re.compile('\d+-\d+-\d+')
            for t in p.finditer(temp):
                tel = t.group()

            if tel == "":
                f = re.compile('\d+-\d+')
                for t in f.finditer(temp):
                    tel = t.group()

            if tel == "":
                f = re.compile('u\'\d+')
                for t in f.finditer(temp):
                    tel = t.group() 
   
            sql = "insert into sp_ba_carshop(`bname`,`name`,`url`,`tel`,`address`) values('"+item["carshopb"][i].encode("utf-8")+"','"+item["carshop"][i].encode("utf-8")+"','"+item["carshopurl"][i].encode("utf-8")+"','"+str(tel)+"','"+item["address"][i].encode("utf-8")+"')"

            print sql
            tx.execute(sql)
    
    def _bitcarmodel_insert(self,tx,item):
        sql = "select id from sp_ba_carstyle where url like '"+item["c1"]+"'"
        tx.execute(sql)
        pid = tx.fetchall()[0]["id"]
        fullUrl = "http://car.bitauto.com"+item["carmodelurl"][0]

        sql = "insert into sp_ba_carmodel(`pid`,`name`,`marketprice`,`url`,`state`) value('"+str(pid)+"','"+item["carmodel"][0].encode("utf-8")+"','"+item["mark"][0].encode("utf-8")+"','"+fullUrl.encode("utf-8")+"','"+str(item["carmodelstate"])+"')"
        print sql
        tx.execute(sql)        

    def _bitlistpage_insert(self,tx,item):
        if len(item["page"]) > 0:
            sql = "update sp_ba_carmodel set maxpages="+str(item["page"][0])+" where url like '%"+item["c1"]+"%'"
        else:
            sql = "update sp_ah_carmodel set maxpages = 1 where url like '%"+item["c1"]+"%'"
        print sql
        tx.execute(sql)
        return item

           
    def _bitprice_insert(self,tx,item):
        carmodelsql = "select id from sp_ba_carmodel where url like '%"+item["carmodelurl"]+"%'"
        tx.execute(carmodelsql)
        cid = ""
        for r in tx.fetchall():
            cid = r["id"]
        if cid == "":
            cid = 0

        darr = item["city"][0].split()
        districtsql = "select id  from sp_ah_district where name like '"+darr[1]+"%' and pname like '%"+darr[0]+"%'"
        tx.execute(districtsql)
        for r in tx.fetchall():
            did = r["id"]
        if did == "":
            did = 0

        carshopsql = "select id from sp_ba_carshop where name like '%"+item["carshop"][0]+"%'"
        tx.execute(carshopsql)
        sid = ""
        for r in tx.fetchall():
            sid = r["id"]
        if sid == "":
            sid = 0

        for row in item["price"]:
            tempprice = row

        p = re.compile('\d+\.\d+')
        for m in p.finditer(tempprice):
            price = float(m.group())
            
        priceSql = "insert into sp_ba_price(`did`,`cid`,`sid`,`price`) values('"+str(did)+"','"+str(cid)+"','"+str(sid)+"','"+str(price)+"')"
        print priceSql
        tx.execute(priceSql)

############################################[ Handle the data from autohome ]#########################################################

    def _contidional_insert(self,tx,item):
	if item.get('brand'):
            tx.execute('insert into sp_ah_brand(`name`,`url`) values(%s,%s)',(item['brand'][0].encode('utf-8'),item['brandurl'][0]))
            pid = tx.lastrowid
            for i in range(len(item["carstyle"])):
                tx.execute('insert into sp_ah_carstyle(`pid`,`name`,`url`) values(%s,%s,%s)',(pid,item["carstyle"][i],item["carstyleurl"][i]))
                           
                
    def _carmodel_insert(self,tx,item):
        for k in range(len(item["carmodel"])):
            urlx = "http://www.autohome.com.cn/"+item["carmodelurl"][k]
            pid = str(item["pid"])
            autopid =  str(item["autohomepid"])
            sql = "insert into sp_ah_carmodel(`pid`,`autohomepid`,`name`,`marketprice`,`url`,`state`) values('"+pid+"','"+autopid+"','"+item['carmodel'][k]+"','"+item['mark'][k]+"','"+urlx+"','"+item["carmodelstate"][k]+"')"
            tx.execute(sql)
        return item

    def _carshop_insert(self,tx,item):
        for k in range(len(item["carshop"])):
            urlx = "http://dealer.autohome.com.cn"+item["carshopurl"][k]
            sql = "insert into sp_ah_carshop(`dname`,`bname`,`name`,`url`,`tel`,`address`) values('"+item['carshopd'][k]+"','"+item['carshopb'][k]+"','"+item['carshop'][k]+"','"+urlx+"','"+item['tel'][k]+"','"+item['address'][k]+"')"
            tx.execute(sql)
    
    def _page_insert(self,tx,item):

        if len(item["page"]) > 0:
            sql = "update sp_ah_carmodel set maxpages="+str(item["page"][0])+" where autohomepid = '"+item["c1"]+"' and url like '%"+item["c2"]+"%'"
        else:
            sql = "update sp_ah_carmodel set maxpages = 1 where autohomepid = '"+item["c1"]+"' and url like '%"+item["c2"]+"%'"
        
        tx.execute(sql)
        return item


    def _price_insert(self,tx,item):
        autohomepid = item["c1"]
        autohomeid = item["c2"]           
        for k in range(len(item["carshop"])):
            tel = item["tel"][k]

            carmodelSql = "select id from sp_ah_carmodel where autohomepid ='"+autohomepid+"' and url like '%"+autohomeid+"%'"
            tx.execute(carmodelSql)
            for row in tx.fetchall():
                if row['id'] != '':
                    cid = row['id']
                else:
                    cid = 0
                         
            districtSql = "select id from sp_ah_district where name like '"+item["town"][k]+"%' and ppname like '%"+item["province"][k]+"%'"
            tx.execute(districtSql)
            for row in tx.fetchall():
                if row['id'] != '':
                    did = row['id']
                else:
                    did = 0

            carshopSql = "select id from sp_ah_carshop where tel ='"+tel+"'"
            tx.execute(carshopSql)
            for row in tx.fetchall():
                if row["id"] != '':
                    sid = row['id']
                else:
                    sid = 0
              
            p = re.compile('\d+\.\d+')
            for m in p.finditer(item["price"][k]):
                price = float(m.group())
            
            priceSql = "insert into sp_ah_price(`did`,`cid`,`sid`,`price`) values('"+str(did)+"','"+str(cid)+"','"+str(sid)+"','"+str(price)+"')"
            tx.execute(priceSql)

    def handle_error(self,item):
        return item
